"""
Case Type   : 系统内部使用工具
Case Name   : 构建cond_func用例数据
Create At   : 2022/03/03
Owner       : zou_jialiang050
Description :
    1.构建数据
    2.清理环境
Expect      :
    1.构建成功
    2.成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.Common import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Constant = Constant()


class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pricomsh = CommonSH("PrimaryDbUser")
        self.primary_node = Node('PrimaryDbUser')
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "source_data")

    def test_ustore(self):
        text = f"-----step1:主备数据expec:成功-----"
        self.log.info(text)
        file_lis = ['vector_base_tab', 'vector_type_tab', 'customer_tab',
                    'prepose_table_002', 'prepose_table_005']
        for file in file_lis:
            self.common.scp_file(self.primary_node, f"{file}", self.target_path)
        sql1 = f"""
        drop table if exists t_ustore_condfunc_case1_1 cascade;
        create table t_ustore_condfunc_case1_1 (
            col_tinyint tinyint,
            col_smallint smallint,
            col_int integer,
            col_bigint bigint,
            col_numeric numeric,
            col_real real,
            col_double double precision,
            col_decimal decimal,
            col_varchar varchar(1000),
            col_char char(30),
            col_nvarchar2 nvarchar2(1000),
            col_text varchar(1000),
            col_timestamptz varchar(1000),
            col_timestamp timestamp with time zone,
            col_date date,
            col_time varchar(1000),
            col_timetz varchar(1000),
            col_interval varchar(1000),
            col_smalldatetine date )
        with (storage_type=ustore) partition by range (col_int)(
        partition vector_base_tab_000_1 values less than (10),
        partition vector_base_tab_000_2 values less than (1357),
        partition vector_base_tab_000_3 values less than (2687),
        partition vector_base_tab_000_4 values less than (maxvalue));
        drop index if exists base_tab_index_01 cascade ;
        drop index if exists base_tab_index_02 cascade ;
        drop index if exists base_tab_index_03 cascade ;
        drop index if exists base_tab_index_04 cascade ;
        drop index if exists base_tab_index_05 cascade ;
        drop index if exists base_tab_index_06 cascade ;
        drop index if exists base_tab_index_07 cascade ;
        drop index if exists base_tab_index_08 cascade ;
        drop index if exists base_tab_index_09 cascade ;
        drop index if exists base_tab_index_10 cascade ;
        drop index if exists condfunc_index_11 cascade ;
        drop index if exists base_tab_index_12 cascade ;
        create index base_tab_index_01 on 
        t_ustore_condfunc_case1_1(col_int) local;
        create index base_tab_index_02 on 
        t_ustore_condfunc_case1_1(col_bigint) local;
        create index base_tab_index_03 on 
        t_ustore_condfunc_case1_1(col_numeric) local;
        create index base_tab_index_04 on 
        t_ustore_condfunc_case1_1(col_double) local;
        create index base_tab_index_05 on 
        t_ustore_condfunc_case1_1(col_real) local;
        create index base_tab_index_06 on 
        t_ustore_condfunc_case1_1(col_nvarchar2) local;
        create index base_tab_index_07 on 
        t_ustore_condfunc_case1_1(col_text) local;
        create index base_tab_index_08 on 
        t_ustore_condfunc_case1_1(col_timestamp) local;
        create index base_tab_index_09 on 
        t_ustore_condfunc_case1_1(col_date) local;
        create index base_tab_index_10 on 
        t_ustore_condfunc_case1_1(col_time) local;
        create index condfunc_index_11 on 
        t_ustore_condfunc_case1_1(col_smallint,col_int,col_char,col_text,
        col_timestamp,col_date) local;
        create index base_tab_index_12 on 
        t_ustore_condfunc_case1_1(col_interval) local;
        copy  t_ustore_condfunc_case1_1 from 
        '{os.path.join(self.target_path, 'vector_base_tab')}' delimiters ',';
        """

        sql2 = f"""
        drop table if exists t_ustore_condfunc_case1_2 cascade;
        create table t_ustore_condfunc_case1_2 (
            col_tinyint tinyint,
            col_smallint smallint,
            col_int integer,
            col_bigint bigint,
            col_money varchar(1000),
            col_numeric numeric,
            col_real real,
            col_double float8 ,
            col_decimal decimal,
            col_varchar varchar(1000),
            col_char char(30),
            col_nvarchar2 nvarchar2(1000),
            col_text varchar(1000),
            col_timestamp varchar(1000),
            col_timestamptz timestamp without time zone,
            col_date date,
            col_time varchar(1000),
            col_timetz varchar(1000),
            col_interval varchar(1000),
            col_tinterval varchar(1000),
            col_smalldatetine varchar(1000),
            col_bytea bytea,
            col_boolean boolean,
            col_inet varchar(1000),
            col_cidr varchar(1000),
            col_bit varchar(100),
            col_varbit varchar(1000),
            col_oid varchar(1000)) with (storage_type=ustore);
            copy  t_ustore_condfunc_case1_2 from 
            '{os.path.join(self.target_path, 'vector_type_tab')}' 
            delimiters ',';"""

        sql3 = f"""
        drop table if exists t_ustore_condfunc_case1_3 cascade;
        create table t_ustore_condfunc_case1_3 (
            c_id int, c_d_id int, c_w_id int, c_first varchar(16), 
            c_middle char(2), c_last varchar(16), c_street_1 varchar(20), 
            c_street_2 varchar(20), c_city varchar(20), c_state char(2), 
            c_zip char(9), c_phone char(16), c_since timestamp, 
            c_credit char(2), c_credit_lim numeric(12,2), 
            c_discount numeric(4,4), c_balance numeric(12,2), 
            c_ytd_payment numeric(12,2), c_payment_cnt int, c_delivery_cnt int, 
            c_data varchar(500))
        with (storage_type=ustore) partition by range (c_id)(
        partition vector_engine_customer_1 values less than (10),
        partition vector_engine_customer_2 values less than (77),
        partition vector_engine_customer_3 values less than (337),
        partition vector_engine_customer_4 values less than (573),
        partition vector_engine_customer_5 values less than (1357),
        partition vector_engine_customer_6 values less than (2033),
        partition vector_engine_customer_7 values less than (2087),
        partition vector_engine_customer_8 values less than (2387),
        partition vector_engine_customer_9 values less than (2687),
        partition vector_engine_customer_10 values less than (2987),
        partition vector_engine_customer_11 values less than (maxvalue));
        create index customer_index_v on 
        t_ustore_condfunc_case1_3(c_w_id, c_d_id, c_id) local;
        create index customer_index_v_2 on 
        t_ustore_condfunc_case1_3(c_city) local;
        create index customer_index_v_3 on 
        t_ustore_condfunc_case1_3(c_credit_lim) local;
        create index customer_index_v_4 on 
        t_ustore_condfunc_case1_3(c_data) local;
        create index ndx_customer_name_v on 
        t_ustore_condfunc_case1_3 (c_w_id, c_d_id, c_last, c_first) local;
        copy  t_ustore_condfunc_case1_3 from 
        '{os.path.join(self.target_path, 'customer_tab')}' delimiters ',';
    """

        sql4 = f"""
        drop table if exists t_ustore_condfunc_case1_4 cascade;
        create table t_ustore_condfunc_case1_4(
            col_smallint smallint,
            col_int integer,
            col_bigint bigint,
            col_numeric_s numeric(18,6),
            col_numeric_1 numeric(16),
            col_numeric_b numeric(20,12),
            col_numeric_n numeric (19),
            col_numeric_2 numeric (19,6),
            col_double double precision,
            col_real real,
            col_char1 char,
            col_char char(37),
            col_charn char(67),
            col_varchar_n varchar(37),
            col_varchar varchar(1000),
            col_text varchar(1000),
            col_date date,
            c_ts_without timestamp without time zone,
            c_ts_with  varchar(1000) )
        with (storage_type=ustore) partition by range (col_int)(
            partition prepose_table_002_1 values less than (2),
            partition prepose_table_002_2 values less than (5),
            partition prepose_table_002_3 values less than (13),
            partition prepose_table_002_4 values less than (358),
            partition prepose_table_002_5 values less than (698),
            partition prepose_table_002_6 values less than (maxvalue));
        drop index if exists prepose_index_002 cascade;
        drop index if exists prepose_index_002_2 cascade;
        drop index if exists prepose_index_002_3 cascade;
        drop index if exists prepose_index_002_4 cascade;
        drop index if exists prepose_index_002_5 cascade;
        create index prepose_index_002 on 
        t_ustore_condfunc_case1_4 (c_ts_without) local;
        create index prepose_index_002_2 on 
        t_ustore_condfunc_case1_4 (col_int) local;
        create index prepose_index_002_3 on 
        t_ustore_condfunc_case1_4 (col_numeric_s)local;
        create index prepose_index_002_4 on 
        t_ustore_condfunc_case1_4 (col_varchar) local;
        create index prepose_index_002_5 on 
        t_ustore_condfunc_case1_4 (col_text) local;
        copy t_ustore_condfunc_case1_4 from 
        '{os.path.join(self.target_path, 'prepose_table_002')}' delimiters  ',';
    """

        sql5 = f"""
        drop table if exists t_ustore_condfunc_case1_5 cascade;
        create table t_ustore_condfunc_case1_5(
        col_smallint smallint,
        col_int integer,
        col_bigint bigint,
        col_numeric_s numeric(18,6),
        col_numeric_1 numeric(16),
        col_numeric_b numeric(20,12),
        col_numeric_n numeric (19),
        col_numeric_2 numeric (19,6),
        col_double double precision,
        col_real real,
        col_char1 char(100),
        col_char char(37),
        col_charn char(67),
        col_varchar_n varchar(37),
        col_varchar varchar(1000),
        col_text varchar(2000),
        col_date date,
        c_ts_without timestamp without time zone,
        c_ts_with varchar(2000) )
        with (storage_type=ustore) partition by range (col_int)(
            partition prepose_table_005_1 values less than (4),
            partition prepose_table_005_2 values less than (7),
            partition prepose_table_005_3 values less than (12),
            partition prepose_table_005_4 values less than (369),
            partition prepose_table_005_5 values less than (569),
            partition prepose_table_005_6 values less than (700),
            partition prepose_table_005_7 values less than (maxvalue));
        create index prepose_index_005 on 
        t_ustore_condfunc_case1_5 (c_ts_without) local;
        create index prepose_index_005_2 on 
        t_ustore_condfunc_case1_5 (col_int) local;
        create index prepose_index_005_3 on 
        t_ustore_condfunc_case1_5 (col_numeric_s) local;
        create index prepose_index_005_4 on 
        t_ustore_condfunc_case1_5 (col_varchar) local;
        create index prepose_index_005_5 on 
        t_ustore_condfunc_case1_5 (col_text) local;
        copy t_ustore_condfunc_case1_5 from 
        '{os.path.join(self.target_path, 'prepose_table_005')}' delimiters ',';
    """
        res1 = self.pricomsh.execut_db_sql(sql1)
        self.log.info(res1)
        res2 = self.pricomsh.execut_db_sql(sql2)
        self.log.info(res2)
        res3 = self.pricomsh.execut_db_sql(sql3)
        self.log.info(res3)
        res4 = self.pricomsh.execut_db_sql(sql4)
        self.log.info(res4)
        res5 = self.pricomsh.execut_db_sql(sql5)
        self.log.info(res5)

        self.assertIn('COPY 2061', res1, 'sql1执行失败')
        self.assertIn('COPY 1977', res2, 'sql2执行失败')
        self.assertIn('COPY 3000', res3, 'sql3执行失败')
        self.assertIn('COPY 458', res4, 'sql4执行失败')
        self.assertIn('COPY 1469', res5, 'sql5执行失败')

    def tearDown(self):
        text = f"-----step2:清理环境;expec:成功-----"
        self.log.info(text)
        del_flag = False
        del_cmd = f'rm -rf {self.target_path};' \
                  f'if [ -d {self.target_path} ]; ' \
                  f'then echo "exists";else echo "not exists"; fi'
        for num in range(10):
            del_res = self.common.get_sh_result(self.primary_node, del_cmd)
            if 'not exists' in del_res:
                del_flag = True
                break
        self.assertTrue(del_flag, '文件删除失败')
        self.log.info(f'----{os.path.basename(__file__)} finish----')
